package com.kcsj.dao.User;


import com.kcsj.domain.user.Customer;
import com.kcsj.domain.user.Role;
import com.kcsj.domain.user.User;
import com.kcsj.util.Condition;
import com.kcsj.util.JdbcHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * @author 胡庆阳
 * 29th Nov. 2020
 */
public class CustomerDao {
    private static final CustomerDao customerDao = new CustomerDao();
    private CustomerDao() {

    }

    public static CustomerDao getInstance() {
        return customerDao;
    }
    //查找customer表中所有数据
    public Collection<User> findAll(Connection conn,List<Condition> conditionList) throws SQLException {
        ArrayList<User> customers = new ArrayList<>();
        //预编译方式进行查询
        StringBuilder s = new StringBuilder("select * from user,customer where user.id = customer.id");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause2(conditionList);
            s.append(whereClause);
        }
        PreparedStatement preparedStatement = conn.prepareStatement(s.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String type = resultSet.getString("type");
            String nickName = resultSet.getString("nickName");
            int role_id = resultSet.getInt("role_id");
            Boolean activationStatus = resultSet.getBoolean("activationStatus");
            String name = resultSet.getString("name");
            String phoneNumber = resultSet.getString("phoneNumber");
            String email = resultSet.getString("email");
            String IDCard = resultSet.getString("IDCard");
            String company = resultSet.getString("company");
            Role role = RoleDao.getInstance().find(role_id,conn);
            Customer customer = new Customer(id,nickName, name, phoneNumber, email, IDCard, company);
            User user = new User(id, username, password, type, role, activationStatus,customer);
            customers.add(user);
        }
        JdbcHelper.close(resultSet, preparedStatement);
        return customers;
    }


    //根据id值查找user表和Customer表中相应数据
    public User find(Integer id, Connection conn) throws SQLException {
        User user = null;
        //预编译方式进行查询
        String s = "select * from user,customer where user.id = customer.id and customer.id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将resultSet转换为json字符串
        if (resultSet.next()){
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String type = resultSet.getString("type");
            String nickName = resultSet.getString("nickName");
            int role_id = resultSet.getInt("role_id");
            Boolean activationStatus = resultSet.getBoolean("activationStatus");
            String name = resultSet.getString("name");
            String phoneNumber = resultSet.getString("phoneNumber");
            String email = resultSet.getString("email");
            String IDCard = resultSet.getString("IDCard");
            String company = resultSet.getString("company");
            Role role = RoleDao.getInstance().find(role_id,conn);
            Customer customer = new Customer(id,nickName, name, phoneNumber, email, IDCard, company);
            user = new User(id, username, password, type, role, activationStatus,customer);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        return user;
    }

    //根据id值查找Customer表中相应数据
    public Customer findInfo(Integer id, Connection conn) throws SQLException {
        Customer customer = null;
        //预编译方式进行查询
        String s = "select * from customer where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将resultSet转换为json字符串
        if (resultSet.next()){
            String nickName = resultSet.getString("nickName");
            String name = resultSet.getString("name");
            String phoneNumber = resultSet.getString("phoneNumber");
            String email = resultSet.getString("email");
            String IDCard = resultSet.getString("IDCard");
            String company = resultSet.getString("company");
            customer = new Customer(id,nickName, name, phoneNumber, email, IDCard, company);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        return customer;
    }

    //修改customer表中的数据
    public boolean update(Customer customer, Connection conn) throws SQLException {
        //预编译方式进行修改数据
        String s = "update customer set nickName = ?,name = ?,phoneNumber = ?,email = ?,IDCard = ?,company = ? where id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,customer.getNickName());
        preparedStatement.setString(2,customer.getName());
        preparedStatement.setString(3,customer.getPhoneNumber());
        preparedStatement.setString(4,customer.getEmail());
        preparedStatement.setString(5,customer.getIDCard());
        preparedStatement.setString(6,customer.getCompany());
        preparedStatement.setInt(7,customer.getId());
        //如果执行影响行数大于0，则修改成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }

    //向Customer表中添加数据
    public Customer add(User user, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        Customer customer = (Customer)user.getActor();
        try {
            String spl = "{CALL addCustomer(?,?,?,?,?,?,?,?,?,?,?,?)}";
            /*call  addCustomer("flx", "123456", "客户", 1, 1, "flx", "胡庆洋", "17660107885", "lxian_fu@163.com", "371525200106111039", "xx公司","111")*/
            CallableStatement callableStatement = conn.prepareCall(spl);
            callableStatement.setString(1,user.getUsername());
            callableStatement.setString(2,user.getPassword());
            callableStatement.setString(3, user.getType());
            callableStatement.setInt(4, user.getRole().getId());
            callableStatement.setBoolean(5, user.getActivationStatus());
            callableStatement.setString(6, customer.getNickName());
            callableStatement.setString(7, customer.getName());
            callableStatement.setString(8, customer.getPhoneNumber());
            callableStatement.setString(9, customer.getEmail());
            callableStatement.setString(10, customer.getIDCard());
            callableStatement.setString(11,customer.getCompany());
            callableStatement.setString(12,user.getCode());
            callableStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }

        //JdbcHelper.close(callableStatement);
        return customer;
    }
    //在customer表根据id删除相应数据
    public boolean delete(Integer id, Connection conn) throws SQLException {
        //预编译方式进行删除数据
        String s = "delete from customer where  id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setInt(1,id);
        //如果执行影响行数大于0，则增加成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }
}
